{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Cover sheet","Main sheet","Viability assessment","Restructuring","Sales to third party","Collateral enforcement","Insolvency proceeding"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":0,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover sheet","index":0,"n_rows":14,"n_cols":9,"rows_kept":14,"cut":false,"hidden":false,"merged":[[13,8,14,10]]}
[]
[]
[]
[]
[]
[]
[]
[null,"Course on Nonperforming Loan Resolution",null,null,null,null,null,null,"NPL Resolution Case Study #2"]
[null,"2021-11-26"]
[]
[null,"Instructor",null,"Karlis Bauze"]
[null,null,null,"Senior Financial Sector Specialist"]
[]
[null,null,null,null,null,null,null,null,"Please enable macros to operate this spreadsheet."]

{"sheet":"Main sheet","index":1,"n_rows":60,"n_cols":11,"rows_kept":60,"cut":false,"hidden":false,"merged":[[16,4,17,4],[1,1,2,2],[20,1,21,2],[23,1,24,2],[47,4,48,4],[40,4,41,4],[27,4,28,4],[34,4,35,4],[6,4,7,4],[27,2,28,3],[40,2,41,3],[47,2,48,3],[34,2,35,3],[6,2,7,3],[56,9,56,10],[57,9,57,10],[21,5,21,6],[21,9,21,10],[53,1,54,2]]}
[]
[null,"Background"]
[null,null,null,null,"Please read the background information in this section, then proceed to \"Viability assessment\" below."]
[]
[null,null,"Loan to be resolved"]
[]
[null,null,"Value in thousands\nof local currency",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Principal outstanding",null,{"$f":"NPV($F$14,G9:K9)+F9","v":6800000},6800000]
[null,null,"Interest outstanding",null,{"$f":"NPV($F$14,G10:K10)+F10","v":33592},33592]
[]
[null,null,"Parameters"]
[1]
[2,null,"Interest rate over time",null,null,0.12,null,"Years to enforce collateral (insolvency)",null,null,4]
[3,null,"Years to enforce collateral",null,null,3,null,"Years to receive liquidation proceeds",null,null,5]
[4]
[null,null,null,null,null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Exchange rate: local currency to USD",null,null,27.19,27.75,28.31,28.88,29.44,30]
[]
[null,"Viability assessment",null,null,null,"Click on \"Start\" on the button to the left and determine:"]
[null,null,null,null,null,"Is the company viable?",null,null,null,{"$f":"IF(ISBLANK('Viability assessment'!H3), \" \", 'Viability assessment'!H3)","v":"No"}]
[]
[null,"Workout options",null,null,null,"Once viability is assessed, click on the \"Update\" button to expand the workout options."]
[null,null,null,null,null,"Click on \"Start\" to explore details of individual options."]
[]
[]
[null,null,"Restructuring",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Principal"]
[null,null,"Interest"]
[null,null,"Sum"]
[]
[]
[null,null,"Sales to third party",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Proposal 1"]
[null,null,"Proposal 2"]
[]
[]
[null,null,"Collateral enforcement",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery"]
[null,null,"Expenses"]
[null,null,"Sum"]
[]
[]
[null,null,"Insolvency proceeding",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery"]
[null,null,"Expenses"]
[null,null,"Sum"]
[]
["Restructuring","Conclusion"]
["Sales to third party",null,null,null,"Edit as needed cell E15 and K14, and regenerate results to answer the following questions:"]
[]
["Insolvency proceeding",null,"The best workout option, assuming collateral takes 3 years to enforce (4 yrs for insolvency), is:"]
[null,null,"                                            … assuming collateral takes 2 years to enforce (3 yrs in the example), is:"]
[]
[null,null,null,null,"What would be the reasons behind?"]

{"sheet":"Viability assessment","index":2,"n_rows":32,"n_cols":15,"rows_kept":32,"cut":false,"hidden":false,"merged":[[1,1,2,2]]}
[]
["Yes","Viability  assessment",null,null,"Analyze material below, and click on the green cell's dropdown menu to determine:"]
["No",null,null,null,"Is the company viable?",null,null,"No"]
[]
[null,"Background",null,null,"Once done, click \"Finish\" to return."]
[]
[null,"The company borrowed USD 250 million (approximately 6.8 billion in local currency) on a 7-year investment loan in 2012."]
[null,"The loan was priced at 12 per cent and secured by a mortgage on a land plot."]
[null,"It used for commercial real estate but the property's occupancy remained below target level."]
[null,"An related, ongoing construction project was also stalled because of a funding deficit."]
[]
[null,"The company made net losses in the run-up to 2015 and was placed on the bank's watchlist."]
[null,"It defaulted on an October 2016 interest payment, and was past due for 2015 taxes."]
[]
[null,"Financial statement"]
[]
[null,null,null,null,2014,2015,"2016 (E)"]
[null,"Sales",null,null,309299,459179,520213]
[null,"EBITDA",null,null,304768,486241,531284]
[null,"EBIT",null,null,269044,429914,452708]
[null,"Net earnings",null,null,-1643267,-2438889,-1078360]
[null,"Key financial ratios (in percentages)",null,null,null,null,null,null,null,"Reference benchmarks*",null,null,"Good","Moderate","Bad"]
[null,"    Compound annual growth rate of revenue",null,null,{"$f":"(G23-F23)/2","v":-17.582990225739326},{"$f":"(F18/E18-1) * 100","v":48.457964623228669},{"$f":"(G18/F18-1) * 100","v":13.291984171750016},null,null,"    Compound annual growth rate of revenue",null,null,"> 5%","-5% to 5%","< -5%"]
[null,"    Net debt / EBITDA",null,null,9.3000000000000007,12.4,12.9,null,null,"    Net debt / EBITDA ratio",null,null,"< 1","1 to 5","> 5"]
[null,"    Debt service coverage ratio",null,null,1,0.8,0.6,null,null,"    Debt service coverage ratio",null,null,"> 1.5","1 to 1.5","< 1"]
[null,null,null,null,null,null,null,null,null,"*: Values adapted from three studies by Deloitte, KPMG, and PwC in Balkan countries."]
[null,"Additional Information"]
[]
[null,"The company's manager was reluctantly cooperative with the bank, and was lacking in financial knowledge."]
[null,"The real estate market showed signs of oversupply, and points to a low probability of the company renting out excess space."]
[]
[null,"Indicate your assessment on the firm's viability in the green cell at the top of the page, then click on \"Finish\" to return to the main page."]

{"sheet":"Restructuring","index":3,"n_rows":30,"n_cols":10,"rows_kept":30,"cut":false,"hidden":false,"merged":[[1,1,2,2],[21,2,22,3]]}
[]
[null,"Restructuring",null,null,"Review the description below on the restructuring process."]
[null,null,null,null,"Once done, click on the \"Finish\" button to return to main page."]
[]
[null,null,"Considering the difficulty the firm experienced, a significant discount needed to be implemented, necessitated by the firm's cash flow."]
[null,null,"The proposed option involves heavily reducing the principal amount from 6.8 to 1.6 million, and forgiving the rest of the loan."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Restructuring",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"\"A\" note"]
[null,null,"    Principal",null,0,206035.5,262813.5,321698.5,382690.5,445789.25]
[null,null,"    Interest",null,0,115465,99093.25,77719.75,51092,18958.25]
[null,null,"\"B\" note"]
[null,null,"    Principal (conditional)",null,0,0,0,0,0,{"$f":"135000*27.2","v":3672000}]
[]
[null,"Now, click on \"Finish\" at top of the spreadsheet to return to the main page."]

{"sheet":"Sales to third party","index":4,"n_rows":12,"n_cols":10,"rows_kept":12,"cut":false,"hidden":false,"merged":[[1,1,2,2],[8,2,9,3]]}
[]
[null,"Sales to third party"]
[null,null,null,null,"Consider the scenario below, then return to the main page:"]
[]
[null,"Background"]
[]
[null,"Two asset management companies offered to purchase the loan:"]
[]
[null,null,"Offers",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Proposal 1",null,2050000,0,0,0,0,0]
[null,null,"Proposal 2",null,2100000,0,0,0,0,0]

{"sheet":"Collateral enforcement","index":5,"n_rows":43,"n_cols":11,"rows_kept":43,"cut":false,"hidden":false,"merged":[[34,10,35,10],[1,1,2,2],[8,6,8,7],[8,4,8,5],[22,4,22,7],[34,2,35,3]]}
[]
[null,"Collateral enforcement"]
[null,null,null,null,"Consider the information below, then return to the main page:"]
[]
[null,null,"Collateral values"]
[]
[null,null,"The collaterals have been reappraised. They will be liquidated for values below if enforced:"]
[]
[null,null,null,null,"Appraisal values",null,"Sales value at enforcement"]
[null,null,null,null,"Original","Updated","Thousands","% of appraisal value"]
[null,null,"Land plot",null,200000,1500000,{"$f":"F11*H11","v":900000},0.6]
[null,null,"Building",null,2500000,4500000,{"$f":"F12*H12","v":3150000},0.7]
[null,null,"Total",null,{"$f":"SUM(E11:E12)","v":2700000},{"$f":"SUM(F11:F12)","v":6000000},{"$f":"SUM(G11:G12)","v":4050000}]
[]
[]
[]
[null,null,"The proceeds are expected to be received in:",null,null,null,{"$f":"'Main sheet'!F15","v":3},"years."]
[]
[null,null,"Collateral enforcement costs"]
[]
[null,null,"Additional costs are to be involved in enforcing the collaterals, over a span of 3 years."]
[]
[null,null,null,null,"Amount"]
[null,null,"Court fees (two levels)",null,189000,3.2,"% of claim or limit of 504,000"]
[null,null,"Bailiff initiation fee ",null,32000,2,"% of claim or limit of 32,000"]
[null,null,"Bailiff service fee",null,405000,10,"% of forced sale value"]
[null,null,"Auction platform fee",null,81000,2,"% of forced sale value"]
[null,null,"Other selling costs",null,40500,1,"% of forced sale value"]
[null,null,"Total",null,{"$f":"SUM(E24:E28)","v":747500}]
[]
[null,null,"The distribution of expenses is indicated as expected below:"]
[]
[null,null,"Expected outcome"]
[]
[null,null,"Collateral enforcement",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21","Total"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery",null,0,0,0,{"$f":"G13","v":4050000},0,0,{"$f":"SUM(F37:J37)","v":4050000}]
[null,null,"Expenses",null,-90000,-119250,-52250,-486000,0,0,{"$f":"E29","v":747500}]
[]
[null,null,"For additional background on collateral enforcement, refer to cross-country information at:"]
[null,null,"http://www.doingbusiness.org/en/data/exploretopics/enforcing-contracts"]
[]
[null,"Now, click on \"Finish\" at top of the spreadsheet to return to the main page."]

{"sheet":"Insolvency proceeding","index":6,"n_rows":74,"n_cols":11,"rows_kept":74,"cut":false,"hidden":false,"merged":[[66,10,67,10],[1,1,2,2],[8,6,8,7],[8,4,8,5],[50,5,50,7],[66,2,67,3]]}
[]
[null,"Insolvency proceeding"]
[null,null,null,"                        Consider the information below, then return to the main page:"]
[]
[null,null,"Collateral values"]
[]
[null,null,"The collaterals have been reappraised. They will be liquidated for less under insolvency:"]
[]
[null,null,null,null,"Appraisal values",null,"Sales value at enforcement"]
[null,null,null,null,"Original","Updated","Thousands","% of appraisal value"]
[null,null,"Land plot",null,200000,1500000,{"$f":"F11*H11","v":600000},0.4]
[null,null,"Building",null,2500000,4500000,{"$f":"F12*H12","v":2250000},0.5]
[null,null,"Total",null,{"$f":"SUM(E11:E12)","v":2700000},{"$f":"SUM(F11:F12)","v":6000000},{"$f":"SUM(G11:G12)","v":2850000}]
[]
[]
[]
[null,null,"Proceeds from collateral sales are to be received in:",null,null,null,{"$f":"'Main sheet'!K14","v":4},"years."]
[]
[null,null,"Liquidation values of assets"]
[]
[null,null,"Balance sheet item",null,"Book value","Estimated recovery rate","Liquidation value"]
[null,null,"Cash",null,0,1,{"$f":"E22*F22","v":0}]
[null,null,"Accounts receivable"]
[null,null,"    0-30 days outstanding",null,178922,0.6,{"$f":"E24*F24","v":107353.2}]
[null,null,"    31-60 days outstanding",null,119282,0.3,{"$f":"E25*F25","v":35784.6}]
[null,null,"    Over 60 days outstanding",null,298204,0.1,29820.400000000001]
[null,null,"Total accounts receivable",null,{"$f":"SUM(E24:E26)","v":596408},null,{"$f":"SUM(G24:G26)","v":172958.19999999998}]
[null,null,"Inventory"]
[null,null,"    Finished goods",null,132547,0.7,{"$f":"E29*F29","v":92782.9}]
[null,null,"    Work in progress",null,265094,0,0]
[null,null,"    Raw materials",null,265094,0.5,132547]
[null,null,"Total inventory",null,{"$f":"SUM(E29:E31)","v":662735},null,{"$f":"SUM(G29:G31)","v":225329.9}]
[null,null,"Other current assets"]
[null,null,"    Prepaid expenses",null,5199,0.5,{"$f":"E34*F34","v":2599.5}]
[null,null,"    Financial investments",null,0,0.3,0]
[null,null,"    Promissory notes",null,0,0.3,0]
[null,null,"Total other current assets",null,{"$f":"SUM(E34:E36)","v":5199},null,{"$f":"SUM(G34:G36)","v":2599.5}]
[null,null,"Total assets",null,{"$f":"SUM(E27,E32,E37)","v":1264342},null,{"$f":"SUM(G27,G32,G37)","v":400887.6}]
[null,null,"Portion of recovery by the bank",null,null,0.77,{"$f":"G38*F39","v":308683.45199999999}]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Proceeds from asset liquidation are to be received in:",null,null,null,{"$f":"'Main sheet'!K15","v":5},"years."]
[]
[null,null,"Insolvency proceeding costs"]
[]
[null,null,null,null,"Amount","Explanation"]
[null,null,"Court fees (two levels)",null,308344,3.2,"% of claim or limit of 504,000"]
[null,null,"Insolvency case opening",null,16000,10,"times minimal living expenses"]
[null,null,"Statement of claims",null,6400,4,"times minimal living expenses"]
[null,null,"Bailiff initiation fee ",null,32000,2,"% of claim or limit of 32,000"]
[null,null,"Arbitrage manager pay",null,29452,2,"times minimal wage for 6 months"]
[null,null,"Liquidator pay",null,206165,2,"times minimal wage for 42 months"]
[null,null,"Sale organizer fee",null,94664,3,"% of forced sale value"]
[null,null,"Extra pay to liquidator / manager",null,79996,3,"% of net recovery value"]
[null,null,"Other selling costs",null,31555,1,"% of forced sale value"]
[null,null,"Total",null,{"$f":"SUM(E52:E60)","v":804576}]
[]
[null,null,"The distribution of expenses over time is indicated as expected over a period of 5 years as below:"]
[]
[null,null,"Expected outcome"]
[]
[null,null,"Insolvency proceeding",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21","Total"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery",null,0,0,0,0,{"$f":"G13","v":2850000},{"$f":"G39","v":308683.45199999999},{"$f":"SUM(F69:J69)","v":3158683.452}]
[null,null,"Expenses",null,-146831,-215914,-68404,-69423,-227641,-76376,{"$f":"E61","v":804576}]
[]
[null,null,"http://www.doingbusiness.org/en/data/exploretopics/resolving-insolvency"]
[]
[null,"Now, click on \"Finish\" at top of the spreadsheet to return to the main page."]